import openpyxl
import pandas as pd
# import numpy as np
from util import QueryCondition,createQueryCondition,num2col,debug
import itertools

class ExcelObj():
    def __init__(self,excel_file=None,sheet='Sheet1'):
        self.excel_file = excel_file 
        self.sheet= sheet

        self.wb = None
    
    def header2Index(self,HeaderName):
        '''
        column header name to index
        https://stackoverflow.com/questions/34296132/how-to-use-field-name-or-column-header-in-openpyxl
        '''
        for n, cell in enumerate(next(self.ws.rows)):
            if cell.value == HeaderName:
                print('meet:',cell.value,n)
                return num2col(n+1)
        return None

    def _load_workbook(self):

        if self.wb is not None:
            return
        
        self.wb = openpyxl.load_workbook(filename = self.excel_file)
        self.ws = self.wb[self.sheet]
        try:
            self.ws2 = self.wb['Sheet2']
        except:
            self.ws2 = None
            print('no sheet2')

    def get_max_seq(self):
        
        self._load_workbook()

        max_row = self.ws.max_row
        col_seq = self.header2Index('序号')

        max_sq_value = self.ws[f'{col_seq}{max_row}'].value
        try:
            max_sq_value = int(max_sq_value)
        except:
            max_sq_value = max_row
            print(f'max seq value {max_sq_value} is not a number, use row max shape:{max_row}')
        print(f'sheet:{self.ws}, max row:{max_row},seq:{col_seq}, max seq={max_sq_value}')
        return col_seq, max_sq_value

    def insert(self,name,pid):
        self._load_workbook()
        
        col_seq, seq_value = self.get_max_seq()
        col_name = self.header2Index('姓名')
        col_id = self.header2Index('身份证号码')
        
        row_current = self.ws.max_row+1
        column_max = self.ws.max_column

        print(f'sheet:{self.ws},\
            last row:{row_current}, max column:{column_max},\
                seq:{col_seq}name:{col_name},pid:{col_id}')
        
        # Data can be assigned directly to cells
        self.ws[f'{col_seq}{row_current}'] = seq_value +1
        self.ws[f'{col_name}{row_current}'] = name
        self.ws[f'{col_id}{row_current}'] = pid

        # print(f'insert column:{[i for i in self.ws.iter_rows()]}')
        
        print('insert row:{}, data:{}'.format(row_current, [cell.internal_value for cell in self.ws[row_current:row_current]]))
        # Rows can also be appended
        # self.ws.append([1, 2, 3])
        
        self.wb.save(self.excel_file)
        return seq_value +1
    
    def ListSheet(self):
        self._load_workbook()
        
        return self.wb.sheetnames

    def _sheet2df(self,ws):
         # for i in self.ws.values:
        #     print(i)
        # print(self.ws.values)
        header= next(ws.values)
        df = pd.DataFrame(itertools.islice(ws.values,1,None),columns=header)
        # df = df.replace(pd.NaT,None)
        # remove heading name
        # df = df[1:]
        # df.columns = df.iloc[0]
        # drop all None row
        df.dropna(axis='index',how='all',inplace=True)
        # drop has not header column
        df = df[df.columns.dropna()]
        return df

    def query(self,qc:QueryCondition):
        self._load_workbook()
            
        print(f'Query condition:{qc}')
        df = self._sheet2df(self.ws)
        
        if self.ws2:
            df2 = self._sheet2df(self.ws2) 
            print('Raw df:\n',df)  
            print('Raw df2:\n',df2)
            df = pd.merge(df,df2,on='身份证号码', how='left')     
            print('join:\n',df)
            if debug:
                df.to_excel('debug.xlsx')
        print('header:\n',df.columns.values )

        def check_NOT_in_header(label):
            if label in df.columns.values:
                return False
            print(f'Label:[{label}] not in {df.columns.values}')
            return True

        mask_condition= [True] * df.shape[0]
        if qc.q_name.data:
            for col in qc.q_name.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col].str.contains(qc.q_name.data)
        if qc.q_gender.data:
            for col in qc.q_gender.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col]== qc.q_gender.data
        
        if qc.q_birth_start.data:
            for col in qc.q_birth_start.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col] >= pd.to_datetime(qc.q_birth_start.data)
        if qc.q_birth_end.data:
            for col in qc.q_birth_end.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col] <= pd.to_datetime(qc.q_birth_end.data)

        if qc.q_education.data:
            for col in qc.q_education.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col] == qc.q_education.data
        
        if qc.q_degree.data:
            for col in qc.q_degree.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col] == qc.q_degree.data

        if qc.q_title.data:
            for col in qc.q_title.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col].str.contains(qc.q_title.data)
        if qc.q_research.data:
            for col in qc.q_research.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col].str.contains(qc.q_research.data)

        
        if qc.q_team_start.data:
            for col in qc.q_team_start.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col] >= pd.to_datetime(qc.q_team_start.data)
        if qc.q_team_end.data:
            for col in qc.q_team_end.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col] <= pd.to_datetime(qc.q_team_end.data)

        if qc.q_item_if.data:
            for col in qc.q_item_if.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col] == qc.q_item_if.data
        
        if qc.q_item_name.data:
            for col in qc.q_item_name.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col].str.contains(qc.q_item_name.data)
        if qc.q_item_id.data:
            for col in qc.q_item_id.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col].str.contains(qc.q_item_id.data)

        if qc.q_item_date_start.data:
            for col in qc.q_item_date_start.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col] >= pd.to_datetime(qc.q_item_date_start.data)
        if qc.q_item_date_end.data:
            for col in qc.q_item_date_end.columnNum:
                if check_NOT_in_header(col): continue
                mask_condition &= df[col] <= pd.to_datetime(qc.q_item_date_end.data)
        
        if qc.q_item_price_start.data:
            for col in qc.q_item_price_start.columnNum:
                if check_NOT_in_header(col): continue
                # if no value, set default to 0
                df.loc[df[col].isna(),col] =9999999
                mask_condition &= df[col] >= int(qc.q_item_price_start.data)
        if qc.q_item_price_end.data:
            for col in qc.q_item_price_end.columnNum:
                if check_NOT_in_header(col): continue
                df.loc[df[col].isna(),col] =9999999
                mask_condition &= df[col] <= int(qc.q_item_price_end.data)

        print('query raw:\n',df[mask_condition])
        return df[mask_condition]

excelObj = ExcelObj()

if __name__ == "__main__":
    obj= ExcelObj("new.xlsx")
    obj.insert('name','1234567')
    print(obj.ListSheet())
    obj.query(createQueryCondition({'q_name':'晓红'}))
    # obj.query(createQueryCondition({'q_name':'王晓'}))
    # obj.query(createQueryCondition({'q_name':'王晓','q_gender':'男'}))